package io.gitee.yaoshanli.engine.db;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Collections;
import java.util.List;

/**
 * @author 姚善利
 * @classname org.jwflow.engine.db.DbHelper
 * @description  数据库操作类
 * @date 2022/9/21 8:35
 */
public class JwDbHelper {
    private static final Logger log = LoggerFactory.getLogger(JwDbHelper.class);
    /**
     * dbutils的QueryRunner对象
     */
    public static QueryRunner runner;

    /**
     * jdbc的数据源
     */
    public static DataSource dataSource;

    public static Connection conn;
    /**
     * setter
     * @param _dataSource
     */
    public static void setDataSource(DataSource _dataSource) {
        dataSource = _dataSource;
        runner= new QueryRunner(dataSource);
    }

    public static void initialize(Object accessObject) {
        if(accessObject == null) return;
        if(accessObject instanceof DataSource) {
            dataSource = (DataSource)accessObject;
            runner= new QueryRunner(dataSource);
        }
    }
    public static void close() {
        if(conn!=null)
        {
            try {
                JdbcHelper.close(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static QueryRunner getRunner()
    {
        return runner;
    }

    /**
     * 返回数据库连接对象
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        if(conn==null)
        {
            conn = JdbcHelper.getConnection(dataSource);
        }
        return conn;
    }

    /**
     * 查询指定列
     * @param column 结果集的列索引号
     * @param sql sql语句
     * @param params 查询参数
     * @return 指定列的结果对象
     */
    public static Object queryObject(int column, String sql, Object... params) {
        Object result;
        try {
            if(log.isDebugEnabled()) {
                log.debug("查询单列数据=\n" + sql);
            }
            result = runner.query(sql, new ScalarHandler(column), params);
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
            throw new RuntimeException(e.getMessage(), e);
        }
        return result;
    }
    /**
     * 查询指定列
     * @param column 结果集的列索引号
     * @param sql sql语句
     * @return 指定列的结果对象
     */
    public static Object queryObject(int column, String sql) {
        Object result;
        try {
            if(log.isDebugEnabled()) {
                log.debug("查询单列数据=\n" + sql);
            }
            result = runner.query(sql, new ScalarHandler(column));
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
            throw new RuntimeException(e.getMessage(), e);
        }
        return result;
    }

    public static boolean isORM() {
        return false;
    }

    /**
     * 查询单条数据
     * @param clazz
     * @param sql
     * @param args
     * @param <T>
     * @return
     */
    public static <T> T queryFirst(Class<T> clazz, String sql, Object... args) {
        try {
            if(log.isDebugEnabled()) {
                log.debug("查询单条记录=\n" + sql);
            }
            return runner.query(sql, new BeanHandler<T>(clazz), args);
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
            return null;
        }
    }

    /**
     * 查询数据列表
     * @param sql
     * @return
     */
    public static String queryString(String sql) {
        try {
            if(log.isDebugEnabled()) {
                log.debug("查询多条记录=\n" + sql);
            }
            return runner.query(sql, new ScalarHandler<>(1));
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
            return "";
        }
    }
    /**
     * 查询数据列表
     * @param sql
     * @return
     */
    public static String queryString(String sql,Object... args) {
        try {
            if(log.isDebugEnabled()) {
                log.debug("查询多条记录=\n" + sql);
            }
            return runner.query(sql, new ScalarHandler<>(1),args);
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
            return "";
        }
    }
    /**
     * 查询数据列表
     * @param sql
     * @return
     */
    public static List<String> queryList(String sql) {
        try {
            if(log.isDebugEnabled()) {
                log.debug("查询多条记录=\n" + sql);
            }
            return runner.query(sql, new ColumnListHandler<String>(1));
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
            return Collections.emptyList();
        }
    }
    /**
     * 查询数据列表
     * @param sql
     * @return
     */
    public static List<String> queryList(String sql, Object... args) {
        try {
            if(log.isDebugEnabled()) {
                log.debug("查询多条记录=\n" + sql);
            }
            return runner.query(sql, new ColumnListHandler<String>(1),args);
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
            return Collections.emptyList();
        }
    }
    /**
     * 查询数据列表
     * @param clazz
     * @param sql
     * @param <T>
     * @return
     */
    public static <T> List<T> queryList(Class<T> clazz, String sql) {
        try {
            if(log.isDebugEnabled()) {
                log.debug("查询多条记录=\n" + sql);
            }
            return runner.query(sql, new BeanListHandler<T>(clazz));
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
            return Collections.emptyList();
        }
    }
    /**
     * 查询数据列表
     * @param clazz
     * @param sql
     * @param args
     * @param <T>
     * @return
     */
    public static <T> List<T> queryList(Class<T> clazz, String sql, Object... args) {
        try {
            if(log.isDebugEnabled()) {
                log.debug("查询多条记录=\n" + sql);
            }
            return runner.query(sql, new BeanListHandler<T>(clazz), args);
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
            return Collections.emptyList();
        }
    }

    /**
     * 查询数量
     * @param sql
     * @param args
     * @return
     */
    public static int queryCount(String sql, Object... args) {
        return executeSql(sql,args);
    }

    /**
     * 查询数量
     * @param sql
     * @return
     */
    public static int queryCount(String sql) {
        return executeSql(sql);
    }
    /**
     * 执行sql
     * @param sql
     * @param args
     * @return
     */
    public static int executeSql(String sql,Object... args)
    {
        try {
            return runner.execute(sql,args);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    public static int executeSql(String sql)
    {
        try {
            return runner.execute(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    public static int update(String sql,Object[] args)
    {
        try {
            return runner.update(sql,args);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    public static int update(String sql)
    {
        try {
            return runner.update(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    public static boolean batch(String sql, Object[][] params)
    {
        try {
            return runner.batch(sql,params).length>0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return false;
    }
}
